package com.hsh.erp.utils;


import com.hsh.erp.anno.Alia;
import com.hsh.erp.anno.SheetInfo;
import com.hsh.erp.constants.filepath.SheetNameConstants;
import com.hsh.erp.domain.file.FileParams;
import lombok.extern.slf4j.Slf4j;
import org.apache.logging.log4j.util.Strings;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.nio.file.Files;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Slf4j
public class POIExcelUtils {

//============================================read方法开始===========================================================


    /**
     * 查找 文件夹目录pathPre 内 有namePre前缀的文件
     *
     * @param pathPre 文件夹目录
     * @param namePre 名称前缀
     * @param clazz
     * @return
     */
    public static <T> List<T> reads(String pathPre, String namePre, Class<T> clazz) {
        return reads(pathPre, namePre, clazz, null);

    }

    /**
     * 查找 文件夹目录pathPre 内 有namePre前缀的文件
     *
     * @param pathPre 文件夹目录
     * @param namePre 名称前缀
     * @param clazz
     * @return
     */
    public static <T> List<T> reads(String pathPre, String namePre, Class<T> clazz, String password) {
        List<String> paths = getPathsByPre(pathPre, namePre);
        List<T> result = new ArrayList<>();
        for (String path : paths) {
            List<T> readList = read(path, clazz, password);
            result.addAll(readList);
        }
        log.info("批量读取数据  {}  条", result.size());
        return result;
    }

    /**
     * 查找 文件夹目录pathPre 内 有namePre前缀的文件
     *
     * @param fileParams
     * @return
     */
    public static <T> List<T> reads(FileParams fileParams) {
        List<String> paths = getPathsByPre(fileParams.getPathPre(), fileParams.getNamePre(), fileParams.getDateStr());
        List<T> result = new ArrayList<>();
        for (String path : paths) {
            List<T> readList = read(path, fileParams.getClazz(), fileParams.getPassword());
            result.addAll(readList);
        }
        log.info("批量读取{}个文件，共{}条数据", paths.size(),result.size());
        return result;
    }


    /**
     * 读取一个excel
     *
     * @param path
     * @param clazz
     * @return
     */
    public static <T> List<T> read(String path, Class<T> clazz) {
        return read(path, clazz, null);
    }

    /**
     * 读取一个excel
     *
     * @param path
     * @param clazz
     * @return
     */
    public static <T> List<T> read(String path, Class<T> clazz, String password) {
        int headerRowIndex = getHeaderRowIndex(clazz);
        return read(path, headerRowIndex, getHeaderRowIndex(clazz) + 1, Integer.MAX_VALUE, clazz, password);
    }

    /**
     * @param path
     * @param headerRowIndex 标题行的下标，从0开始
     * @param startRowIndex  第一行数据的下标，从0开始
     * @param endRowIndex    最后一行数据的下标，从0开始
     * @param clazz
     * @return
     */
    public static <T> List<T> read(String path, int headerRowIndex, int startRowIndex, int endRowIndex, Class<T> clazz, String password) {
        //获取sheetName
        String sheetName = getSheetName(clazz);
        //获取表头别名集合headerAliaMap
        Map<String, String> headerAliaMap = getHeaderAliaMap(clazz);
        List<Map<String, String>> readList = read(path, sheetName, headerRowIndex, startRowIndex, endRowIndex, headerAliaMap, password);
        List<T> result = new ArrayList<>(readList.size());
        //将读取结果 转为具体 实体类集合
        for (Map<String, String> map : readList) {
            T t = MyBeanUtils.mapToBean(map, clazz);
            if (t != null) {
                result.add(t);
            }
        }
        log.info("\n读取路径：{}\n读取数据  {}  条\n读取数据：{}", path, result.size(), readList);
        return result;

    }

    /**
     * @param path
     * @param sheetName
     * @param headerRowIndex 标题行的下标，从0开始
     * @param startRowIndex  第一行数据的下标，从0开始
     * @param endRowIndex    最后一行数据的下标，从0开始
     * @param headerAliaMap  类的别名集：key为字段名 ，value为别名
     * @return
     */
    public static List<Map<String, String>> read(String path, String sheetName, int headerRowIndex
            , int startRowIndex, int endRowIndex, Map<String, String> headerAliaMap, String password) {
        //结果集
        List resultList = new ArrayList();
        File file = new File(path);
        //该路径找不到文件
        if (!file.isFile()) {
            return resultList;
        }
        //将要读取的文件转为Workbook
        Workbook workbook = getOrCreateWorkbook(path, password);
        //要读取的文件中一个sheet都没有
        if (workbook.getNumberOfSheets() == 0) {
            return resultList;
        }
        //获得要读取的sheet
        Sheet sheet = workbook.getSheet(sheetName);
        sheet = (sheet == null) ? workbook.getSheetAt(0) : sheet;
        // 获取表格表头的字段名
        List<String> headerList = readRow(sheet.getRow(headerRowIndex));
        if (headerList.isEmpty()) {
            return resultList;
        }
        //确认读取行数范围
        if (startRowIndex > endRowIndex) {
            return resultList;
        }
        startRowIndex = Math.max(startRowIndex, sheet.getFirstRowNum());
        endRowIndex = Math.min(endRowIndex, sheet.getLastRowNum());
        //遍历数据
        for (int i = startRowIndex; i <= endRowIndex; i++) {
            if (i == headerRowIndex) {
                continue;
            }
            Row row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            // 获得数据，并根据字段名下标对应插入
            Map<String, String> cellValues = new LinkedHashMap<>();
            for (int j = 0; j < headerList.size(); j++) {
                //根据excel中的表头内容找到字段名
                String alia = headerList.get(j);
                String fileName = getKeyByValue(headerAliaMap, alia);
                fileName = Strings.isBlank(fileName) ? alia : fileName;
                //读取到的数据
                String cellValue = getCellValue(row.getCell(j));
                //每行就是一个对象，先使用map接收
                cellValues.put(fileName, cellValue);
            }
            resultList.add(cellValues);
        }
        //关闭workbook
        try {
            workbook.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return resultList;

    }


    /**
     * 获得一行数据
     *
     * @param row
     * @return
     */
    public static List<String> readRow(Row row) {
        List<String> cellValues = new ArrayList<>();
        if (row == null) {
            return cellValues;
        }
        for (int i = 0; i < row.getLastCellNum(); i++) {
            String cellValue = getCellValue(row.getCell(i));
            cellValues.add(cellValue);
        }
        return cellValues;
    }
//============================================read方法结束===========================================================


//============================================write方法开始==========================================================


    /**
     * 向文件中写入数据
     * 若当前文件存在
     *
     * @param path
     * @param list
     * @param clazz
     * @param <T>
     */
    public static <T> int write(String path, List<T> list, Class<T> clazz) {
        //之前已经录入的元素不再插入
        List<T> readList = read(path, clazz, null);
        List<T> writeList = new ArrayList<>(list);
        writeList.removeAll(readList);
        log.info("写入路径：{}", path);
        log.info("去除重复数据{}条，写入数据{}条，数据为：{}", list.size() - writeList.size(), writeList.size(), writeList);
        if (writeList.isEmpty()) {
            return 0;
        }
        Map<String, String> headerAliaMap = getHeaderAliaMap(clazz);
        List<String> headerAliaList = new ArrayList<>(headerAliaMap.values());
        int headerRowIndex = getHeaderRowIndex(clazz);
        try {
            //新建或者读取一个workbook
            Workbook workbook = getOrCreateWorkbook(path, null);
            //新建或者读取一个sheet
            Sheet sheet = getOrCreateSheet(workbook, getSheetName(clazz));
            //写入表头
            writeRow(sheet, headerRowIndex, headerAliaList, true);
            //写入数据
            writeData(sheet, writeList);
            FileOutputStream out = new FileOutputStream(path);
            workbook.write(out);
            workbook.close();
            out.close();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return writeList.size();
    }

    /**
     * 写入数据
     *
     * @param sheet
     * @param list  写入的数据
     * @param <T>
     * @return
     */
    public static <T> void writeData(Sheet sheet, List<T> list) {
        int i = sheet.getLastRowNum();
        for (T t : list) {
            writeRow(sheet, i + 1, objectToList(t, false), false);
            i++;
        }
    }

    /**
     * 写入一行
     * 如果是表头，无需另外设置样式
     * 如果不是表头，与该行的表头设置为相同样式
     *
     * @param rowData  1行数据
     * @param isHeader 是否为表头
     */
    public static void writeRow(Sheet sheet, int rowIndex, List rowData, boolean isHeader) {
        Row row = sheet.createRow(rowIndex);
        int i = 0;
        for (Object o : rowData) {
            Cell cell = row.createCell(i);
            cell.setCellValue(o == null ? "" : o.toString());
            Cell headerRowCell = sheet.getRow(0).getCell(i);
            //格式和当列表头保持一致
            if (!isHeader && headerRowCell != null) {
                cell.setCellStyle(headerRowCell.getCellStyle());
            }
            i++;
        }
    }
//============================================write方法结束==========================================================


//============================================私有方法开始============================================================

    /**
     * 获得类的别名集 key为字段名 ，value为别名
     *
     * @param clazz
     * @param <T>
     * @return
     */
    private static <T> Map<String, String> getHeaderAliaMap(Class<T> clazz) {
        Field[] fields = clazz.getDeclaredFields();
        Map<String, String> map = new LinkedHashMap<>();
        for (Field field : fields) {
            Alia anno = field.getAnnotation(Alia.class);
            if (anno != null) {
                map.put(field.getName(), anno.value());
            }
        }
        return map;
    }

    /**
     * 注释:  获取数据类型
     *
     * @param cell
     * @return java.lang.String
     * @author yangyongzhuo 2022/11/25 13:26
     */
    private static String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }

        int cellType = cell.getCellType();
        if (cellType == Cell.CELL_TYPE_FORMULA) { // 表达式类型
            return cell.getCellFormula();
        } else if (cellType == Cell.CELL_TYPE_NUMERIC) {
//            return String.valueOf(cell.getNumericCellValue());
            double d = cell.getNumericCellValue();
            String s = String.format("%.0f", d);
            Matcher matcher = Pattern.compile("[+-]?\\d+.0+?").matcher(s);
            if (matcher.find()) {
                s = String.valueOf((int) d);
            }
            return s;
        } else if (cellType == Cell.CELL_TYPE_STRING) {
            return cell.getStringCellValue();
        } else if (cellType == Cell.CELL_TYPE_BLANK) {
            return "";
        } else if (cellType == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cellType == Cell.CELL_TYPE_ERROR) {
            return "ERROR";
        } else {
            return cell.toString().trim();
        }
    }

    /**
     * 根据map的value获得key
     *
     * @param map
     * @param value
     * @param <K>
     * @param <V>
     * @return
     */
    private static <K, V> K getKeyByValue(Map<K, V> map, V value) {
        for (Map.Entry<K, V> entry : map.entrySet()) {
            if (value.equals(entry.getValue())) {
                return entry.getKey();
            }
        }
        return null;
    }

    /**
     * 对象转成集合，对象属性值->集合元素
     *
     * @param t
     * @param isHeader
     * @param <T>
     * @return
     */
    public static <T> List objectToList(T t, boolean isHeader) {
        List list = new ArrayList();
        Field[] fields = t.getClass().getDeclaredFields();
        for (Field field : fields) {
            //设置私有属性可修改
            field.setAccessible(true);
            if (field.getAnnotation(Alia.class) == null) {
                continue;
            }
            try {
                if (isHeader) {
                    list.add(field.getName());
                } else {
                    list.add(field.get(t));
                }
            } catch (IllegalAccessException e) {
                throw new RuntimeException(e);
            }
        }
        return list;
    }

    /**
     * 查找 文件夹目录pathPre 内 有namePre前缀的 文件名集
     *
     * @param pathPre 文件夹目录
     * @param namePre 名称前缀
     * @return
     */
    private static List<String> getPathsByPre(String pathPre, String namePre) {
        return getPathsByPre(pathPre, namePre, namePre);
    }

    /**
     * 查找 文件夹目录pathPre 内 有namePre前缀的 文件名集
     *
     * @param pathPre 文件夹目录
     * @param namePre 名称前缀
     * @param dateStr 指定格式的日期
     * @return
     */
    private static List<String> getPathsByPre(String pathPre, String namePre, String dateStr) {
        List<String> paths = new ArrayList<>();
        try {
            String[] names = new File(pathPre).list();
            if (names == null) {
                return paths;
            }
            for (String name : names) {
                if (name.startsWith(namePre) && DataUtils.isInclude(name, dateStr)) {
                    paths.add(pathPre + name);
                }
            }
        } catch (Exception e) {
            log.error("获取文件夹内的路径失败:{}\n失败原因{}：", pathPre, e.getMessage());
        }
        return paths;
    }


    /**
     * .xls文件不能有密码，会报错
     * excel有密码时
     * 根据path后缀新建一个workbook
     *
     * @param path
     * @param password excel的密码
     * @return
     */
    static Workbook getOrCreateWorkbook(String path, String password) {
        Workbook workbook = new HSSFWorkbook();
        File file = new File(path);
        try {
            //创建或者读取 workbook
            if (file.isFile()) {
                workbook = WorkbookFactory.create(Files.newInputStream(file.toPath()), password);
            } else {
                workbook = new XSSFWorkbook();
            }
        } catch (Exception e) {
            log.error("创建或者读取文件失败：{}", e);
        }

        return workbook;
    }

    /**
     * 读取或者创建一个sheet
     *
     * @param workbook
     * @param sheetName
     * @return
     */
    private static Sheet getOrCreateSheet(Workbook workbook, String sheetName) {
        Sheet sheet = workbook.getSheet(sheetName);
        if (sheet == null) {
            sheet = workbook.createSheet(sheetName);
        }
        return sheet;
    }

    /**
     * 获取sheetName
     *
     * @param clazz
     * @param <T>
     * @return
     */
    private static <T> String getSheetName(Class<T> clazz) {
        String sheetName = SheetNameConstants.DEFAULT_SHEET_NAME;
        SheetInfo anno = clazz.getAnnotation(SheetInfo.class);
        if (anno == null) {
            return sheetName;
        }
        return anno.name();
    }

    /**
     * 获取 标题行所在行数  默认为0
     *
     * @param clazz
     * @param <T>
     * @return
     */
    private static <T> int getHeaderRowIndex(Class<T> clazz) {
        int headerRowIndex = 0;
        SheetInfo anno = clazz.getAnnotation(SheetInfo.class);
        if (anno == null) {
            return headerRowIndex;
        }
        return anno.headerRowIndex();
    }

//============================================私有方法结束============================================================
}
